package com.bringway.p2p.dao.impl;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import com.bringway.framework.dao.P2PJdbcDao;
import com.bringway.global.util.StringUtils;
import com.bringway.p2p.dao.JhnhkDao;

import net.sf.json.JSONObject;
/**  
 * @类名 JhnhkDaoImpl  
 * @功能 TODO  计划内还款管理 dao接口实现类
 * @作者 js 
 * @日期 2016年8月5日 上午9:44:04  
 *    
 */
@Repository
public class JhnhkDaoImpl extends P2PJdbcDao implements JhnhkDao {
	/**
	 * 日志
	 */
	Logger logger=Logger.getLogger(JhnhkDaoImpl.class);

	/* 
	 * @方法名  queryJhnMjqDkList 
	 * @功能   TODO 查询 应还款列表
	 * @param params
	 * @return  
	 * @see com.bringway.p2p.dao.JhnhkDao#queryJhnMjqDkList(net.sf.json.JSONObject)  
	 */
	@Override
	public List<Map<String, Object>> queryJhnMjqDkList(JSONObject params) {
		int start=params.optInt("start");
		int limit=params.optInt("limit");
		String startDate=params.optString("startDate");
		String endDate=params.optString("endDate");
		String c_xmmc=params.optString("c_xmmc");
		String c_zqmc=params.optString("c_zqmc");
		String c_jkr=params.optString("c_jkr");
		String dlsjhm=params.optString("dlsjhm");
		String c_xmzt=params.optString("c_xmzt");
		StringBuffer sql=new StringBuffer();
		sql.append("SELECT T1.ID,T1.USER_NAME,T1.SJHM,T1.JHN_XM_ID XMID,T1.JHN_XM_ZQID ZQID,T1.XM_MJ_FPJE,T4.KYJE,T2.REF_CGID");
		sql.append(",T2.XM_NAME,T2.XM_NH_SYL,T2.XM_JE,T2.XM_QX_DAY,T2.XM_QX_MONTH,date_format(T2.XM_FB_DATE,'%Y-%m-%d %H:%i:%s') XM_FB_DATE,T2.XM_ZT,T2.XM_SQ_USER_ID");
		sql.append(",T3.ZQ_NAME,T3.ZQ_JE,T3.ZQ_OPER_USER_ID,date_format(T3.ZQ_OPER_DATE,'%Y-%m-%d %H:%i:%s') ZQ_OPER_DATE,T3.ZQ_OWNER_XM");
		sql.append(",case when 1=1 then (select ZQ_FBJE from t_zhfw_jhn_xmzq where REF_JHN_XM_ID=t1.JHN_XM_ID AND REF_JHN_ZQ_ID=T1.JHN_XM_ZQID) END ZQ_FBJE");
		sql.append(" FROM t_zhfw_jhn_mjq_dk T1 LEFT JOIN t_zhfw_jhn_xm T2 ON T1.JHN_XM_ID=T2.ID");
		sql.append(" LEFT JOIN t_zhfw_jhn_zq T3 ON T1.JHN_XM_ZQID=T3.ID");
		sql.append(" LEFT JOIN t_zhfw_jbxx T4 ON T1.SJHM=T4.SJHM");
		sql.append(" WHERE T1.DK_STATE='01'");
		Map<String, Object> paramMap=new HashMap<>();
		paramMap.put("start", start);
		paramMap.put("limit", limit);
		if (StringUtils.isNotEmpty(startDate)) {
			sql.append(" AND date_format(T2.XM_FB_DATE,'%Y-%m-%d %H:%i:%s')>=:startDate");
			paramMap.put("startDate", startDate+" 00:00:00");
		}
		if (StringUtils.isNotEmpty(endDate)) {
			sql.append(" AND date_format(T2.XM_FB_DATE,'%Y-%m-%d %H:%i:%s')<=:endDate");
			paramMap.put("endDate", endDate+" 23:59:59");
		}
		if (StringUtils.isNotEmpty(c_xmmc)) {
			sql.append(" AND T2.XM_NAME like :c_xmmc");
			paramMap.put("c_xmmc", "%"+c_xmmc+"%");
		}
		if (StringUtils.isNotEmpty(c_zqmc)) {
			sql.append(" AND T3.ZQ_NAME like :c_zqmc");
			paramMap.put("c_zqmc", "%"+c_zqmc+"%");
		}
		if (StringUtils.isNotEmpty(c_xmzt)) {
			sql.append(" AND T2.XM_ZT =:c_xmzt");
			paramMap.put("c_xmzt", c_xmzt);
		}
		if (StringUtils.isNotEmpty(c_jkr)) {
			sql.append(" AND T3.ZQ_OWNER_XM like :c_jkr");
			paramMap.put("c_jkr", "%"+c_jkr+"%");
		}
		if (StringUtils.isNotEmpty(dlsjhm)) {
			sql.append(" AND T1.SJHM =:dlsjhm");
			paramMap.put("dlsjhm", dlsjhm);
		}
		sql.append(" ORDER BY T2.XM_FB_DATE DESC LIMIT :start,:limit");
		logger.info("queryJhnMjqDkList sql is "+sql.toString());
		logger.info("queryJhnMjqDkList paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
	}

	/* 
	 * @方法名  getJhnJhnMjqDkCount 
	 * @功能   TODO 获取 应还款列表 记录总数
	 * @param params
	 * @return  
	 * @see com.bringway.p2p.dao.JhnhkDao#getJhnJhnMjqDkCount(net.sf.json.JSONObject)  
	 */
	@SuppressWarnings("deprecation")
	@Override
	public int getJhnJhnMjqDkCount(JSONObject params) {
		String startDate=params.optString("startDate");
		String endDate=params.optString("endDate");
		String c_xmmc=params.optString("c_xmmc");
		String c_zqmc=params.optString("c_zqmc");
		String c_jkr=params.optString("c_jkr");
		String dlsjhm=params.optString("dlsjhm");
		String c_xmzt=params.optString("c_xmzt");
		StringBuffer sql=new StringBuffer();
		sql.append("SELECT COUNT(*) FROM t_zhfw_jhn_mjq_dk T1");
		sql.append(" LEFT JOIN t_zhfw_jhn_xm T2 ON T1.JHN_XM_ID=T2.ID");
		sql.append(" LEFT JOIN t_zhfw_jhn_zq T3 ON T1.JHN_XM_ZQID=T3.ID");
		sql.append(" WHERE T1.DK_STATE='01'");
		Map<String, Object> paramMap=new HashMap<>();
		if (StringUtils.isNotEmpty(startDate)) {
			sql.append(" AND date_format(T2.XM_FB_DATE,'%Y-%m-%d %H:%i:%s')>=:startDate");
			paramMap.put("startDate", startDate+" 00:00:00");
		}
		if (StringUtils.isNotEmpty(endDate)) {
			sql.append(" AND date_format(T2.XM_FB_DATE,'%Y-%m-%d %H:%i:%s')<=:endDate");
			paramMap.put("endDate", endDate+" 23:59:59");
		}
		if (StringUtils.isNotEmpty(c_xmmc)) {
			sql.append(" AND T2.XM_NAME like :c_xmmc");
			paramMap.put("c_xmmc", "%"+c_xmmc+"%");
		}
		if (StringUtils.isNotEmpty(c_zqmc)) {
			sql.append(" AND T3.ZQ_NAME like :c_zqmc");
			paramMap.put("c_zqmc", "%"+c_zqmc+"%");
		}
		if (StringUtils.isNotEmpty(c_xmzt)) {
			sql.append(" AND T2.XM_ZT =:c_xmzt");
			paramMap.put("c_xmzt", c_xmzt);
		}
		if (StringUtils.isNotEmpty(c_jkr)) {
			sql.append(" AND T3.ZQ_OWNER_XM like :c_jkr");
			paramMap.put("c_jkr", "%"+c_jkr+"%");
		}
		if (StringUtils.isNotEmpty(dlsjhm)) {
			sql.append(" AND T1.SJHM =:dlsjhm");
			paramMap.put("dlsjhm", dlsjhm);
		}
		logger.info("getJhnJhnMjqDkCount sql is "+sql.toString());
		logger.info("getJhnJhnMjqDkCount paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}

	/* 
	 * @方法名  getJhnhkJe 
	 * @功能   TODO 获取 已还款某条记录
	 * @param params
	 * @return  
	 * @see com.bringway.p2p.dao.JhnhkDao#getJhnhkJe(net.sf.json.JSONObject)  
	 */
	@Override
	public Map<String, Object> getJhnhkJe(JSONObject params) {
		//String username=params.optString("jkryhm");
		String dlsjhm=params.optString("dlsjhm");
		String xmid=params.optString("xmid");
		String zqid=params.optString("zqid");
		String hkzl=params.optString("hkzl");
		String hkzt=params.optString("hkzt");
		StringBuffer sql=new StringBuffer();
		sql.append("SELECT SUM(YHK_JE) YHKJE FROM t_zhfw_jhn_jkr_hk WHERE 1=1");
		Map<String, Object> paramMap=new HashMap<>();
		/*if (StringUtils.isNotEmpty(username)) {
			sql.append(" AND USER_NAME =:username");
			paramMap.put("username", username);
		}*/
		if (StringUtils.isNotEmpty(dlsjhm)) {
			sql.append(" AND SJHM =:dlsjhm");
			paramMap.put("dlsjhm", dlsjhm);
		}
		if (StringUtils.isNotEmpty(xmid)) {
			sql.append(" AND REF_XM_ID =:xmid");
			paramMap.put("xmid", xmid);
		}
		if (StringUtils.isNotEmpty(zqid)) {
			sql.append(" AND REF_XM_ZQID =:zqid");
			paramMap.put("zqid", zqid);
		}
		if (StringUtils.isNotEmpty(hkzl)) {
			sql.append(" AND HK_ZL =:hkzl");
			paramMap.put("hkzl", hkzl);
		}
		if (StringUtils.isNotEmpty(hkzt)) {
			sql.append(" AND HK_STATE=:hkzt");
			paramMap.put("hkzt", hkzt);
		}
		logger.info("getJhnhkJe sql is "+sql.toString());
		logger.info("getJhnhkJe paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().queryForMap(sql.toString(), paramMap);
	}

	/* 
	 * @方法名  getJbxxInfo 
	 * @功能   TODO 获取 用户账户信息
	 * @param params
	 * @return  
	 * @see com.bringway.p2p.dao.JhnhkDao#getJbxxInfo(net.sf.json.JSONObject)  
	 */
	@Override
	public Map<String, Object> getJbxxInfo(JSONObject params) {
		String dlsjhm=params.optString("dlsjhm");
		String sql="SELECT KYJE,DJJE,ZHYE FROM t_zhfw_jbxx WHERE SJHM=:dlsjhm";
		Map<String, Object> paramMap=new HashMap<>();
		paramMap.put("dlsjhm", dlsjhm);
		logger.info("getJbxxKyje sql is "+sql);
		logger.info("getJbxxKyje paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().queryForMap(sql, paramMap);
	}

	/* 
	 * @方法名  updateJbxxInfo 
	 * @功能   TODO 更新 用户账户信息
	 * @param params
	 * @return  
	 * @see com.bringway.p2p.dao.JhnhkDao#updateJbxxInfo(net.sf.json.JSONObject)  
	 */
	@Override
	public int updateJbxxInfo(JSONObject params) {
		String dlsjhm=params.optString("dlsjhm");
		String hkje=params.optString("hkje");
		String sql="UPDATE t_zhfw_jbxx SET KYJE=KYJE-:hkje,ZHYE=ZHYE-:hkje WHERE SJHM=:dlsjhm";
		Map<String, Object> paramMap = new HashMap<>();
		paramMap.put("dlsjhm", dlsjhm);
		paramMap.put("hkje", hkje);
		logger.info("updateJbxxInfo sql is "+sql);
		logger.info("updateJbxxInfo paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().update(sql, paramMap);
	}

	/* 
	 * @方法名  insertJhnHkxx 
	 * @功能   TODO 插入 还款记录
	 * @param params
	 * @return  
	 * @see com.bringway.p2p.dao.JhnhkDao#insertJhnHkxx(net.sf.json.JSONObject)  
	 */
	@Override
	public String insertJhnHkxx(JSONObject params) {
		String id=getUUId();
		String username=params.optString("jkryhm");
		String sjhm=params.optString("dlsjhm");
		String xmid=params.optString("xmid");
		String zqid=params.optString("zqid");
		String hkje=params.optString("hkje");
		String fpje=params.optString("fpje");
		String hkzt=params.optString("hkzt");
		String hkzl=params.optString("hkzl");
		StringBuffer sql=new StringBuffer();
		sql.append("INSERT INTO t_zhfw_jhn_jkr_hk(ID, USER_NAME, SJHM, REF_XM_ID, REF_XM_ZQID, REF_XM_MJ_FPJE, YHK_JE, HK_DATE, HK_STATE, HK_ZL)");
		sql.append(" VALUES(:id,:username,:sjhm,:xmid,:zqid,:fpje,:hkje,now(),:hkzt,:hkzl)");
		Map<String, Object> paramMap=new HashMap<>();
		paramMap.put("id", id);
		paramMap.put("username", username);
		paramMap.put("sjhm", sjhm);
		paramMap.put("xmid", xmid);
		paramMap.put("zqid", zqid);
		paramMap.put("fpje", fpje);
		paramMap.put("hkje", hkje);
		paramMap.put("hkzt", hkzt);
		paramMap.put("hkzl", hkzl);
		logger.info("insertJhnHkxx sql is "+sql.toString());
		logger.info("insertJhnHkxx paramMap is "+paramMap);
		//this.getNamedParameterJdbcTemplate().update(sql.toString(), paramMap, generatedKeyHolder)
		int insert=this.getNamedParameterJdbcTemplate().update(sql.toString(), paramMap);
		if (insert>0) {
			return id;
		}
		return null;
	}

	/* 
	 * @方法名  insertZcDetails 
	 * @功能   TODO 插入 用户资产变动详情
	 * @param params
	 * @return  
	 * @see com.bringway.p2p.dao.JhnhkDao#insertZcDetails(net.sf.json.JSONObject)  
	 */
	@Override
	public String insertZcDetails(JSONObject params) {
		int id=this.getZcDetailsMaxId();
		id++;
		String username = params.optString("jkryhm");
        String oJe = params.optString("hkje");
        String cId = params.optString("hkxxid");
        String oDjje = params.optString("DJJE");
        String oKyje = params.optString("KYJE");
        String oZhye = params.optString("ZHYE");
        String hkzl=params.optString("hkzl");
        String cJe="-"+oJe ;
        Double fZhye=Double.valueOf(oZhye)-Double.valueOf(oJe);
        Double fKyje=Double.valueOf(oKyje)-Double.valueOf(oJe);
		StringBuffer sql  =  new StringBuffer();
		sql.append(" INSERT INTO t_zhfw_zc_details (ID, USERNAME, ZC_CHANGE_TYPE, ZC_CHANGE_DATE, ZC_CHANGE_JE");
		sql.append(", ZC_FINAL_ZHYE, ZC_CHANGE_ID, ZC_OCCUR_JE, ZC_ORIGINAL_DJJE, ZC_ORIGINAL_KYJE, ZC_ORIGINAL_ZHYE");
		sql.append(", ZC_FINAL_DJJE, ZC_FINAL_KYJE) VALUES (:ID, :USERNAME, :ZC_CHANGE_TYPE, NOW(), :ZC_CHANGE_JE");
		sql.append(", :ZC_FINAL_ZHYE, :ZC_CHANGE_ID, :ZC_OCCUR_JE, :ZC_ORIGINAL_DJJE, :ZC_ORIGINAL_KYJE, :ZC_ORIGINAL_ZHYE");
		sql.append(", :ZC_FINAL_DJJE, :ZC_FINAL_KYJE) ");
		Map<String,Object> paramMap = new HashMap<String,Object>();
		paramMap.put("ID", id);
		paramMap.put("USERNAME", username);
		if ("01".equals(hkzl)) {
			paramMap.put("ZC_CHANGE_TYPE", "26");
		} else if("02".equals(hkzl)){
			paramMap.put("ZC_CHANGE_TYPE", "27");
		} else if("03".equals(hkzl)){
			paramMap.put("ZC_CHANGE_TYPE", "28");
		}
		paramMap.put("ZC_CHANGE_JE",cJe );
		paramMap.put("ZC_FINAL_ZHYE", fZhye);
		paramMap.put("ZC_CHANGE_ID", cId);
		paramMap.put("ZC_OCCUR_JE", oJe);
		paramMap.put("ZC_ORIGINAL_DJJE", oDjje);
		paramMap.put("ZC_ORIGINAL_KYJE", oKyje);
		paramMap.put("ZC_ORIGINAL_ZHYE", oZhye);
		paramMap.put("ZC_FINAL_DJJE", oDjje);
		paramMap.put("ZC_FINAL_KYJE", fKyje);
		
		logger.info("insertZcDetails sql is : " + sql.toString());
		logger.info("insertZcDetails paramMap is : " + paramMap);
		int insert=this.getNamedParameterJdbcTemplate().update(sql.toString(), paramMap);
		if (insert>0) {
			return Integer.toString(id);
		}
		return null;
	}
	/**
	 * @作者 js  
	 * @方法名 getZcDetailsMaxId  
	 * @功能 TODO 获取 资产变动表 主键最大值
	 * @return
	 */
	private int getZcDetailsMaxId(){
		StringBuffer sql = new StringBuffer();
		Map<String,String> paramMap = new HashMap<>();
		sql.append(" select case when max(convert(id,signed)) is null then 0 ");
		sql.append(" else max(convert(id,signed)) end maxId ");
		sql.append(" from t_zhfw_zc_details ");
		logger.info("t_zhfw_zc_details sql is " + sql.toString());
		return this.getNamedParameterJdbcTemplate().queryForObject(sql.toString(), paramMap, Integer.class);
	}

	/* 
	 * @方法名  updateJhnxm 
	 * @功能   TODO 更新 计划内项目状态
	 * @param params
	 * @return  
	 * @see com.bringway.p2p.dao.JhnhkDao#updateJhnxm(net.sf.json.JSONObject)  
	 */
	@Override
	public int updateJhnxm(JSONObject params) {
		String xmid=params.optString("xmid");
		String xmzt=params.optString("xmzt");
		String sql="UPDATE t_zhfw_jhn_xm SET XM_ZT=:xmzt where ID=:xmid";
		Map<String,String> paramMap = new HashMap<>();
				paramMap.put("xmzt", xmzt);
				paramMap.put("xmid", xmid);
		logger.info("updateJhnxm sql is " + sql);
		logger.info("updateJhnxm paramSource is " + paramMap);
		return this.getNamedParameterJdbcTemplate().update(sql, paramMap);
	}

	/* 
	 * @方法名  updateJhnxmcg 
	 * @功能   TODO 更新 项目草稿 项目状态
	 * @param params
	 * @return  
	 * @see com.bringway.p2p.dao.JhnhkDao#updateJhnxmcg(net.sf.json.JSONObject)  
	 */
	@Override
	public int updateJhnxmcg(JSONObject params) {
		String cgid=params.optString("cgid");
		String xmzt=params.optString("xmzt");
		String sql="UPDATE t_zhfw_jhn_xm_cg SET XM_ZT=:xmzt where ID=:cgid";
		Map<String,String> paramMap = new HashMap<>();
		paramMap.put("xmzt", xmzt);
		paramMap.put("cgid", cgid);
		logger.info("updateJhnxmcg sql is " + sql);
		logger.info("updateJhnxmcg paramSource is " + paramMap);
		return this.getNamedParameterJdbcTemplate().update(sql, paramMap);
	}

	/* 
	 * @方法名  queryJhnhkmxList 
	 * @功能   TODO 查询 还款明细列表
	 * @param params
	 * @return  
	 * @see com.bringway.p2p.dao.JhnhkDao#queryJhnhkmxList(net.sf.json.JSONObject)  
	 */
	@Override
	public List<Map<String, Object>> queryJhnhkmxList(JSONObject params) {
		int start=params.optInt("start");
		int limit=params.optInt("limit");
		String startDate=params.optString("startDate");
		String endDate=params.optString("endDate");
		String q_xmmc=params.optString("q_xmmc");
		String q_xmbh=params.optString("q_xmbh");
		String q_zqmc=params.optString("q_zqmc");
		String q_gyh=params.optString("q_gyh");
		String q_hkzt=params.optString("q_hkzt");
		String q_hkzl=params.optString("q_hkzl");
		StringBuffer sql=new StringBuffer();
		sql.append("SELECT T1.ID,T1.USER_NAME,T1.SJHM,T2.XM_NAME,T2.ORDER,T3.ZQ_NAME,T1.REF_XM_MJ_FPJE,T1.YHK_JE,date_format(T1.HK_DATE,'%Y-%m-%d %H:%i:%s') HK_DATE");
		sql.append(",T1.HK_STATE,T1.HK_ZL FROM t_zhfw_jhn_jkr_hk T1");
		sql.append(" LEFT JOIN t_zhfw_jhn_xm T2 ON T1.REF_XM_ID=T2.ID");
		sql.append(" LEFT JOIN t_zhfw_jhn_zq T3 ON T1.REF_XM_ZQID=T3.ID WHERE 1=1");
		Map<String, Object> paramMap=new HashMap<>();
		paramMap.put("start", start);
		paramMap.put("limit", limit);
		if (StringUtils.isNotEmpty(startDate)) {
			sql.append(" AND date_format(T1.HK_DATE,'%Y-%m-%d %H:%i:%s') >=:startDate");
			paramMap.put("startDate", startDate+" 00:00:00");
		}
		if (StringUtils.isNotEmpty(endDate)) {
			sql.append(" AND date_format(T1.HK_DATE,'%Y-%m-%d %H:%i:%s') <=:endDate");
			paramMap.put("endDate", endDate+" 23:59:59");
		}
		if (StringUtils.isNotEmpty(q_xmmc)) {
			sql.append(" AND T2.XM_NAME like :q_xmmc");
			paramMap.put("q_xmmc", "%"+q_xmmc+"%");
		}
		if (StringUtils.isNotEmpty(q_xmbh)) {
			sql.append(" AND T2.ORDER like :q_xmbh");
			paramMap.put("q_xmbh", "%"+q_xmbh+"%");
		}
		if (StringUtils.isNotEmpty(q_zqmc)) {
			sql.append(" AND T3.ZQ_NAME like :q_zqmc");
			paramMap.put("q_zqmc", "%"+q_zqmc+"%");
		}
		if (StringUtils.isNotEmpty(q_gyh)) {
			sql.append(" AND T1.USER_NAME like :q_gyh");
			paramMap.put("q_gyh", "%"+q_gyh+"%");
		}
		if (StringUtils.isNotEmpty(q_hkzt)) {
			sql.append(" AND T1.HK_STATE =:q_hkzt");
			paramMap.put("q_hkzt", q_hkzt);
		}
		if (StringUtils.isNotEmpty(q_hkzl)) {
			sql.append(" AND T1.HK_ZL =:q_hkzl");
			paramMap.put("q_hkzl", q_hkzl);
		}
		sql.append(" ORDER BY T1.HK_DATE DESC LIMIT :start,:limit");
		logger.info("queryJhnhkmxList sql is "+sql.toString());
		logger.info("queryJhnhkmxList paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
	}

	/* 
	 * @方法名  getJhnhkmxCount 
	 * @功能   TODO 获取还款明细列表 记录总数
	 * @param params
	 * @return  
	 * @see com.bringway.p2p.dao.JhnhkDao#getJhnhkmxCount(net.sf.json.JSONObject)  
	 */
	@SuppressWarnings("deprecation")
	@Override
	public int getJhnhkmxCount(JSONObject params) {
		String startDate=params.optString("startDate");
		String endDate=params.optString("endDate");
		String q_xmmc=params.optString("q_xmmc");
		String q_xmbh=params.optString("q_xmbh");
		String q_zqmc=params.optString("q_zqmc");
		String q_gyh=params.optString("q_gyh");
		String q_hkzt=params.optString("q_hkzt");
		String q_hkzl=params.optString("q_hkzl");
		StringBuffer sql=new StringBuffer();
		sql.append("SELECT COUNT(*) FROM t_zhfw_jhn_jkr_hk T1");
		sql.append(" LEFT JOIN t_zhfw_jhn_xm T2 ON T1.REF_XM_ID=T2.ID");
		sql.append(" LEFT JOIN t_zhfw_jhn_zq T3 ON T1.REF_XM_ZQID=T3.ID WHERE 1=1");
		Map<String, Object> paramMap=new HashMap<>();
		if (StringUtils.isNotEmpty(startDate)) {
			sql.append(" AND date_format(T1.HK_DATE,'%Y-%m-%d %H:%i:%s') >=:startDate");
			paramMap.put("startDate", startDate+" 00:00:00");
		}
		if (StringUtils.isNotEmpty(endDate)) {
			sql.append(" AND date_format(T1.HK_DATE,'%Y-%m-%d %H:%i:%s') <=:endDate");
			paramMap.put("endDate", endDate+" 23:59:59");
		}
		if (StringUtils.isNotEmpty(q_xmmc)) {
			sql.append(" AND T2.XM_NAME like :q_xmmc");
			paramMap.put("q_xmmc", "%"+q_xmmc+"%");
		}
		if (StringUtils.isNotEmpty(q_xmbh)) {
			sql.append(" AND T2.ORDER like :q_xmbh");
			paramMap.put("q_xmbh", "%"+q_xmbh+"%");
		}
		if (StringUtils.isNotEmpty(q_zqmc)) {
			sql.append(" AND T3.ZQ_NAME like :q_zqmc");
			paramMap.put("q_zqmc", "%"+q_zqmc+"%");
		}
		if (StringUtils.isNotEmpty(q_gyh)) {
			sql.append(" AND T1.USER_NAME like :q_gyh");
			paramMap.put("q_gyh", "%"+q_gyh+"%");
		}
		if (StringUtils.isNotEmpty(q_hkzt)) {
			sql.append(" AND T1.HK_STATE =:q_hkzt");
			paramMap.put("q_hkzt", q_hkzt);
		}
		if (StringUtils.isNotEmpty(q_hkzl)) {
			sql.append(" AND T1.HK_ZL =:q_hkzl");
			paramMap.put("q_hkzl", q_hkzl);
		}
		logger.info("getJhnhkmxCount sql is "+sql.toString());
		logger.info("getJhnhkmxCount paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}

	/**
	 * @作者 js  
	 * @方法名 isYhAndYh  
	 * @功能 TODO 判断 应还本息是否等于已还本息
	 * @param params
	 * @return
	 */
	@Override
	public boolean isYhAndYh(JSONObject params) {
		String xmid=params.optString("xmid");
		StringBuffer sql=new StringBuffer();
		sql.append("SELECT CASE WHEN ROUND((SELECT (SELECT SUM(XM_MJ_FPJE) FROM t_zhfw_jhn_mjq_dk");
		sql.append(" WHERE JHN_XM_ID=:xmid)*(1+XM_NH_SYL*XM_QX_DAY/36500) YHBX");
		sql.append(" FROM t_zhfw_jhn_xm WHERE ID=:xmid),2)=");
		sql.append("(SELECT SUM(YHK_JE) YHBX FROM t_zhfw_jhn_jkr_hk WHERE REF_XM_ID=:xmid AND HK_STATE='01')");
		sql.append(" THEN true else false END ISFQ FROM dual");
		Map<String, Object> paramMap=new HashMap<>();
		paramMap.put("xmid", xmid);
		logger.info("isYhAndYh sql is "+sql.toString());
		logger.info("isYhAndYh paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().queryForObject(sql.toString(), paramMap, Boolean.class);
	}

	/**
	 * @作者 js  
	 * @方法名 updateJhnzq  
	 * @功能 TODO 释放债权 更新
	 * @param params
	 * @return
	 */
	@Override
	public int updateJhnzq(JSONObject params) {
		String zqid=params.optString("zqid");
		String fbje=params.optString("fbje");
		String sql="UPDATE t_zhfw_jhn_zq SET ZQ_YYJE=ZQ_YYJE-:fbje,ZQ_KYJE=ZQ_KYJE+:fbje WHERE ID=:zqid";
		Map<String, Object> paramMap=new HashMap<>();
		paramMap.put("zqid", zqid);
		paramMap.put("fbje", fbje);
		logger.info("updateJhnzq sql is "+sql);
		logger.info("updateJhnzq paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().update(sql, paramMap);
	}

	@Override
	public List<Map<String, Object>> queryJhnhkzqList(JSONObject params) {
		String xmid=params.optString("xmid");
		StringBuffer sql=new StringBuffer();
		sql.append("SELECT T2.ID,T2.ZQ_NAME,T2.ZQ_OWNER_XM,T2.ZQ_OWNER_SJHM,T2.ZQ_OWNER_SFZ_NUM,date_format(T2.ZQ_START_DATE,'%Y-%m-%d %H:%i:%s') ZQ_START_DATE");
		sql.append(",date_format(T2.ZQ_END_DATE,'%Y-%m-%d %H:%i:%s') ZQ_END_DATE,T2.ZQ_JE,T2.ZQ_TYPE,T2.ZQ_OPER_USER_ID,date_format(T2.ZQ_OPER_DATE,'%Y-%m-%d %H:%i:%s') ZQ_OPER_DATE,T2.ZQ_STATE,T2.ZH_REF_JHN_ZQ_ID");
		sql.append(",T2.ZQ_DJJE,T2.ZQ_YYJE,T2.ZQ_KYJE,T1.XM_MJ_FPJE,case when 1=1 then (select sum(YHK_JE) from t_zhfw_jhn_jkr_hk ");
		sql.append("where REF_XM_ID=t1.JHN_XM_ID AND REF_XM_ZQID=T1.JHN_XM_ZQID AND HK_STATE='01') end YHK_JE FROM t_zhfw_jhn_mjq_dk T1");
		sql.append(" LEFT JOIN t_zhfw_jhn_zq T2 ON T1.JHN_XM_ZQID=T2.ID WHERE T1.DK_STATE='01' AND T1.JHN_XM_ID=:xmid");
		Map<String, Object> paramMap=new HashMap<>();
		paramMap.put("xmid", xmid);
		logger.info("queryJhnhkzqList sql is "+sql.toString());
		logger.info("queryJhnhkzqList paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
	}

	@Override
	public int getJhnhkzqCount(JSONObject params) {
		String xmid=params.optString("xmid");
		String sql="SELECT COUNT(*) FROM t_zhfw_jhn_mjq_dk WHERE DK_STATE='01' AND JHN_XM_ID=:xmid";
		Map<String, Object> paramMap=new HashMap<>();
		paramMap.put("xmid", xmid);
		logger.info("getJhnhkzqCount sql is "+sql);
		logger.info("getJhnhkzqCount paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().queryForInt(sql, paramMap);
	}
}
